﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//using log4net;
using IDML.BusinessObject;

namespace IDML.DataAccess
{
    public class PatientDAL
    {
        private DBConnection _dbConnect;

        public PatientDAL()
        {
            _dbConnect = new DBConnection();
        }

        public List<PatientBO> Patient_SearchWithText(string SearchText)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            PatientBO patient = null;
            List<PatientBO> patientList = null;
            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("Patient_SearchWithText", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@SearchText", SqlDbType.VarChar).Value = SearchText;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    patient = new PatientBO();
                    patient.PatientId = int.Parse(row["PatientId"].ToString());
                    patient.PatientRefNo = row["PatientRefNo"].ToString();
                    patient.FirstName = row["FiirstName"].ToString();
                    patient.LastName = row["LastName"].ToString();
                    patient.PhoneNumber = row["PhoneNumber"].ToString();
                    patient.Age = int.Parse(row["Age"].ToString());
                    patientList.Add(patient);
                }
                return patientList;
            }
            catch (Exception ex)
            {
                string error = ex.Message;
                //Log.Error("GetUser", ex);
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            return patientList;
        }

        public int Patient_Register(PatientBO patientBO)
        {
            SqlConnection connection = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand insertcommand = new SqlCommand("Patient_Register", connection);
                insertcommand.CommandType = CommandType.StoredProcedure;

                insertcommand.Parameters.Add("@TitleID", SqlDbType.Int).Value = patientBO.TitleID;
                insertcommand.Parameters.Add("@FiirstName", SqlDbType.NVarChar).Value = patientBO.FirstName;
                insertcommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = patientBO.LastName;
                insertcommand.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = patientBO.PhoneNumber;
                insertcommand.Parameters.Add("@Age", SqlDbType.Int).Value = patientBO.Age;
                insertcommand.Parameters.Add("@Gender", SqlDbType.Bit).Value = patientBO.Gender;
                insertcommand.Parameters.Add("@IUserID", SqlDbType.Int).Value = patientBO.IUserID;
                insertcommand.Parameters.Add("@UUserID", SqlDbType.Int).Value = patientBO.IUserID;

                _dbConnect.CommitTransaction();
                int patid = Convert.ToInt32(insertcommand.ExecuteScalar());
                _dbConnect.CloseConnection();
                return patid;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            
        }

        public List<PatientBO> Patient_LoadDetails(int patID)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            PatientBO patient = null;
            List<PatientBO> patientList = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("Patient_SearchWithID", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@PatientID", SqlDbType.Int).Value = patID;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();
                               
                patient = new PatientBO();
                patientList = new List<PatientBO>();

                patient.PatientId = int.Parse(ds.Tables[0].Rows[0]["PatientId"].ToString());
                patient.PatientRefNo = ds.Tables[0].Rows[0]["PatientRefNo"].ToString();
                patient.FirstName = ds.Tables[0].Rows[0]["FiirstName"].ToString();
                patient.LastName = ds.Tables[0].Rows[0]["LastName"].ToString();
                patient.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                patient.Age = Convert.ToInt32(ds.Tables[0].Rows[0]["Age"].ToString());

                patientList.Add(patient);

                return patientList;
            }
            catch (Exception ex)
            {
                string error = ex.Message;
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            return patientList;
        }

        public List<PatientBO> Patient_Search(PatientBO pationtBo)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            PatientBO patient = null;
            List<PatientBO> patientList = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("Patient_Search", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@PatientId", SqlDbType.Int).Value = pationtBo.PatientId;
                selectCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = pationtBo.FirstName;
                selectCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = pationtBo.LastName;
                selectCommand.Parameters.Add("@SpecimenNo", SqlDbType.NVarChar).Value = pationtBo.SpecimenNo;
                selectCommand.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = pationtBo.PhoneNumber;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();

                patient = new PatientBO();
                patientList = new List<PatientBO>();


                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    patient = new PatientBO();
                    patient.PatientId = int.Parse(row["PatientId"].ToString());
                    patient.PatientRefNo = row["PatientRefNo"].ToString(); 
                    patient.FirstName = row["FiirstName"].ToString();
                    patient.LastName = row["LastName"].ToString();
                    patient.PhoneNumber = row["PhoneNumber"].ToString();
                    patient.SpecimenNo =  row["SpecimenNo"].ToString();
                    patient.Age = row["Age"].ToString() == null ? 0 : int.Parse(row["Age"].ToString());
                    patient.Gender = row["Gender"].ToString() == null ? 1 : int.Parse(row["Gender"].ToString());

                    patientList.Add(patient);
                }
                return patientList;
            }
            catch (Exception ex)
            {
                string error = ex.Message;
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            return patientList;
        }



    }
}
